The SQL Statements are entered within the SQL Statement data entry editor. Multiple SQL Queries can be separated by using the ;character and the SQL parameters are denoted by using a
$prefix. An entry box for each parameter will be created automatically under the SQL Parameters section. You can specify an XPath for each SQL parameter to indicate the location of the parameter data in the FactBase. For each dynamic parameter in the SQL statement it is important to set the type of data that this will contain. For example, it may a text, numeric or date value. This can be set using the drop-down at the right of each parameter. If a date type value (date, time or time-stamp) is being used then the value in the XML data must be stored in the W3C Schema format. If the data is originating from a form, then this format can be derived using the in-built 'Value Conversions' functionality within the WebMaker Studio. You can specify the location where the response from the query should be inserted using the to location parameter. You can use the Merge Option setting to determine how the information will be inserted. The response from the SQL Statements will be wrapped using the following document format:
<sql_result>
<statement>
<status outcome="success">
<record_count>1</record_count>
<generated_keys>
<record>
<value>2</value>
</record>
</generated_keys>
</status>
</statement>
<statement>
<status outcome="success"/>
<record>
<client>1</client>
<forename>Fred</forename>
<surname>Bloggs</surname>
<age>21</age>
</record>
<record>
<client>2</client>
<forename>Brenda</forename>
<surname>Bloggs</surname>
<age>20</age>
</record>
</statement>
</sql_result>
The sql_result element wraps the responses from one or more queries. For each statement, you will notice a separate statement element. Within each statement element a status element will indicate the outcome of each query using the outcome attribute. For INSERT and UPDATE queries, there will be additional elements to indicate the number of records that were affected via the record_count element. For INSERT queries you will also notice a generated_keys element. This element will contain details of the generated keys in the database resulting from the INSERT query. For SELECT queries, the ResultSet is returned as a list of record elements.
You may also notice a final statement that can indicate the outcome of an implicit ROLLBACK operation on the database if an error is detected.
Using Stored Procedures
This action also supports calling certain types of stored procedures, using SQL Statement syntax of the form {call procedure_name($param1, $param2)}. It is important to ensure the parameters listed are in the correct order for the Stored Procedure.
When used in this mode, an additional dropdown will be shown alongside each SQL Parameter to indicate whether it is an IN, OUT, or IN OUT parameter. Again, the options selected should match that of the stored procedure being called.
Once the stored procedure call has completed, the values of any OUT or IN OUT parameters will be placed back into the data in the location indicated by the SQL Parameter XPath. This is in addition to having the sql_result fragment added to the data as detailed above. For stored procedure OUT parameters, it is also possible to set the parameter type to a special value of 'Result Set', which should be used if the stored procedure has a result set output parameter. In this case, the appropriate 'record' XML structure will be placed at the indicated location in the data.
COMMIT and ROLLBACK
All SQL statements for the same Database, contained within a RuleBase, will execute in the same transaction. Once all the rules have been processed, a ROLLBACK will be automatically issued, and any uncommitted changes will be lost.
Therefore, it is very important to include an explicit COMMIT statement in your rules whenever you are performing database insert or update operations. If your rules were initially created for you by any WebMaker wizards, then you should find that a specific COMMIT rule has already been added. You can of course add any other explicit COMMIT or ROLLBACK statements as required.
Database Connection
You can use the Select link against the Database field to open the database connection window. To set up a new database click the Setup New Databaselink, which will show the following screen for you to provide the relevant information.
Once you have defined a connection, you can reuse this connection for subsequent actions that use the same database. In this case, you just need to select the entry from the Existing Databases drop down, rather than creating a new connection.
Once a connection has been selected for the SQL Statement action, its name will be displayed against the Database field, and the Change link will appear to allow a different connection to be selected.
If you need to change the details of a database connection (for example, the connection URL or username and password details) then this can also be done via the Database connection window. Just select the edit icon on the right of the existing database entry you wish to adjust.